package cn.iocoder.mssql.gentype;

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import org.junit.Test;

import java.sql.*;

/*
  JDBC执行MSSQL兼容模式存储过程，参数是TABLE类型
  CREATE TYPE test_type AS TABLE ( ID INT, pid INT);

  CREATE OR REPLACE PROCEDURE pro_2 ( pageindex test_type,  OUT total VARCHAR ) AS
  BEGIN
  total := '222';
  raise info '%',total;
  END;
   */
public class GenTypeDemo {

    /*
     输出：org.postgresql.util.PSQLException: ERROR: type test_type is not composite
     */
    @Test
    public void test1() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://172.16.55.175:8888/zxsdb";
        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url, "zxs", "Vastdata#0730");
        CallableStatement cs = conn.prepareCall("call pro_2 (?,?)");

        Object[][] arrayData = {{1, 1}};
        Struct st = conn.createStruct("test_type", arrayData);
        cs.setObject(1, st, Types.STRUCT);
        cs.registerOutParameter(2, Types.VARCHAR);

        cs.execute();
        String total = cs.getString(2);
        System.out.println(total);
    }

    /*
        输出：org.postgresql.util.PSQLException: ERROR: function pro_2(test_type[]) does not exist
     */
    @Test
    public void test2() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://172.16.55.175:8888/zxsdb";
        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url, "zxs", "Vastdata#0730");
        CallableStatement cs = conn.prepareCall("call pro_2 (?,?)");

        Object[][] arrayData = {{1, 1}};
        Array st = conn.createArrayOf("test_type", arrayData);
        cs.setObject(1, st, Types.ARRAY);
        cs.registerOutParameter(2, Types.VARCHAR);

        cs.execute();
        String total = cs.getString(2);
        System.out.println(total);
    }


    /*
        输出：org.postgresql.util.PSQLException: ERROR: function pro_2(test_type[]) does not exist
     */
    @Test
    public void test4() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://172.16.55.175:8888/zxsdb";
        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url, "zxs", "Vastdata#0730");
        CallableStatement cs = conn.prepareCall("call pro_2 (?,?)");

        String pageindexJson = "[{\"ID\":1,\"pid\":100},{\"ID\":2,\"pid\":200}]";
        cs.setObject(1, pageindexJson);
        cs.registerOutParameter(2, Types.VARCHAR);

        cs.execute();
        String total = cs.getString(2);
        System.out.println(total);
    }


    /*
    成功

    SQL server测试，存储过程转：
    CREATE PROCEDURE pro_2
    @pageindex test_type READONLY,  -- 表值参数
    @total VARCHAR(255) OUTPUT       -- 输出参数
    AS
    BEGIN
        SET @total = '222'; -- 设置输出参数的值
    END;

     输出：Total: 222
     */
    @Test
    public void test3() {
        String url = "jdbc:sqlserver://172.16.55.186:1433;databaseName=master;encrypt=false";
        try (Connection con = DriverManager.getConnection(url, "sa", "Vastdata#0730");
             CallableStatement cstmt = con.prepareCall("{call dbo.pro_2(?, ?)}")) {
            // 创建表类型
            SQLServerDataTable table = new SQLServerDataTable();
            table.addColumnMetadata("ID", Types.INTEGER);
            table.addColumnMetadata("pid", Types.INTEGER);

            // 填充数据
            table.addRow(1, 100);
            table.addRow(2, 200);

            // 设置参数
            cstmt.setObject(1, table);
            cstmt.registerOutParameter(2, Types.VARCHAR);

            // 执行存储过程
            cstmt.execute();

            // 获取输出参数
            String total = cstmt.getString(2);
            System.out.println("Total: " + total);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void test5() throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://172.16.55.175:8888/db_joe";
        Class.forName(driver).newInstance();
        Connection conn = DriverManager.getConnection(url, "zxs", "Vastdata#0730");
        CallableStatement cs = conn.prepareCall("call pro_1 (?,?,?)");
        cs.setInt(1, 1);
        cs.setInt(2, 2);
        cs.registerOutParameter(3, Types.VARCHAR);
        try {
            for (int i = 0; i < 6; i++) {
                cs.execute();
                String total = cs.getString(3);
                System.out.println("time:" + i + "，result:" + total);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            cs.close();
            conn.close();
        }
    }

}
